package com.bupt.service;

import com.bupt.bean.DeviceType;
import com.bupt.bean.Role;
import com.bupt.bean.User;
import com.bupt.bean.UserVo;
import com.bupt.config.*;
import com.bupt.dao.DeviceTypeRepository;
import com.bupt.dao.EventRepository;
import com.bupt.vo.AlarmStrategyVo;
import com.bupt.vo.DeviceLogVo;
import com.bupt.vo.DeviceVo;
import com.bupt.vo.EventVo;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

@Service
public class PageService {
    @Autowired
    EventRepository eventRepository;

    @PersistenceContext
    private EntityManager entityManager;

    @Autowired
    private DeviceTypeRepository deviceTypeRepository;

    /**
     *      * 事件页面列表分页查询
     *      * @param page 当前页码
     *      * @param size 页面显示个数
     *      * @param queryPageRequest 查询条件
     *      * @return 页面列表
     *      
     */
    public QueryResponseResult findList(int page, int size, QueryPageRequest queryPageRequest) {
        String count = "";

        if (queryPageRequest == null) {
            queryPageRequest = new QueryPageRequest();
        }
        //分页参数  前台有的分页是0开始 有的是1开始
        if (page <= 0) {
            page = 1;
        }
        page = page - 1;
        if (size <= 0) {
            size = 6;
        }
        //开始时间 结束时间 条件查询
        String endTime = queryPageRequest.getStopDate();
        String end=" 23:59:59";
        String startTime = queryPageRequest.getBeginDate();
        String timeSql = "";
        if (startTime != null && !startTime.trim().equals("") && endTime != null && !endTime.trim().equals("")) {
            timeSql = "AND e.creat_time BETWEEN  ('" + startTime + "') AND ('" + endTime +end+ "')";
        }

        //分页查询sql
        String sql = "SELECT  dt.device_type_name,e.event_id,DATE_FORMAT(e.creat_time,'%Y-%m-%d %H:%i:%s')AS creat_time,e.position,e.value," +
                "e.event_desc,d.device_name,dc.channel_index,es.const_desc as eventStatus,et.const_desc AS eventType,e.status,e.error_flag " +
                "FROM event_t e ,device_t d,device_channel_t dc,device_type_t dt, (SELECT const_value,const_desc FROM sys_const_t WHERE                        const_group_name  ='EVENT_STATUS')es,(SELECT const_value,const_desc FROM sys_const_t WHERE const_group_name                     ='EVENT_TYPE')et" +
                " WHERE e.device_id=d.device_id AND d.device_type_id=dt.device_type_id AND dc.device_id=e.device_id AND es.const_value=e.status AND dc.device_channel_id=e.device_channel_id AND et.const_value=e.event_type   " + timeSql+"ORDER  BY e.event_id DESC" + " LIMIT ";
        Query query = entityManager.createNativeQuery(sql + page * size + "," + size);
        List<EventVo> eventtList = query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();//转为Map
        //总数sql
        String countsql = "SELECT  COUNT(event_id) " +
                "FROM event_t e ,device_t d,device_channel_t dc, (SELECT const_value,const_desc FROM sys_const_t WHERE                        const_group_name  ='EVENT_STATUS')es,(SELECT const_value,const_desc FROM sys_const_t WHERE const_group_name                     ='EVENT_TYPE')et" +
                " WHERE e.device_id=d.device_id AND dc.device_id=e.device_id AND dc.device_channel_id=e.device_channel_id  AND es.const_value=e.status AND et.const_value=e.event_type  " + timeSql;
        Query countQuery = entityManager.createNativeQuery(countsql);
        if (countQuery.getResultList().size() > 0) {
            count = countQuery.getResultList().get(0).toString();

        }

        /*String endTime = queryPageRequest.getStopDate();
        String startTime = queryPageRequest.getBeginDate();
        //由于需要加时间段条件查询  百度了下
        Specification specification = new Specification() {
            @Override
            public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder cb) {
                //增加筛选条件
                Predicate predicate = cb.conjunction();
                //起始日期
                 if (startTime != null && !startTime.trim().equals("")) {
                    predicate.getExpressions().add(cb.greaterThanOrEqualTo(root.get("creatTime").as(String.class),
                            startTime));
                }
                //结束日期
                if (endTime != null && !endTime.trim().equals("")) {
                    predicate.getExpressions().add(cb.lessThanOrEqualTo(root.get("creatTime").as(String.class), endTime));
                }
                return predicate;
            }
        };
        //以下是正常分页写法
        Pageable pageable = PageRequest.of(page,size, Sort.by("eventId").descending());
        Page<Event> all = eventRepository.findAll(specification,pageable);
        QueryResult queryResult = new QueryResult();
        queryResult.setList(all.getContent());//数据列表
        queryResult.setTotal(all.getTotalElements());//数据总记录数*/

        //插入结果集
        QueryResult queryResult = new QueryResult();
        queryResult.setList(eventtList);//数据列表
        queryResult.setTotal(Integer.valueOf(count));
        QueryResponseResult queryResponseResult = new QueryResponseResult(CommonCode.SUCCESS, queryResult);
        return queryResponseResult;
    }


    /**
     *      * 分页查询设备日志列表
     *      * @param page 当前页码
     *      * @param size 页面显示个数
     *      * @param queryPageRequest 查询条件
     *      * @return 页面列表
     *      
     */
    public QueryResponseResult findDeviceLogList(int page, int size, QueryPageRequest queryPageRequest) {
        String count = "";

        if (queryPageRequest == null) {
            queryPageRequest = new QueryPageRequest();
        }
        //分页参数  前台有的分页是0开始 有的是1开始
        if (page <= 0) {
            page = 1;
        }
        page = page - 1;
        if (size <= 0) {
            size = 6;
        }
        //开始时间 结束时间 条件查询
        String endTime = queryPageRequest.getStopDate();
        String end=" 23:59:59";
        String startTime = queryPageRequest.getBeginDate();
        String logType = queryPageRequest.getLogType();
        String deviceId = queryPageRequest.getDeviceId();
        String deviceTypeId = queryPageRequest.getDeviceTypeId();
        String timeSql = " ";
        if (startTime != null && !startTime.trim().equals("") && endTime != null && !endTime.trim().equals("")) {
            timeSql = " AND dl.creat_time BETWEEN  ('" + startTime + "') AND ('" + endTime +end+ "') ";
        }
        if (logType != null && !logType.trim().equals("")){
            timeSql+=" AND dl.log_type="+logType;
        }
        if (deviceId != null && !deviceId.trim().equals("")){
            timeSql+=" AND d.device_id="+deviceId;
        }
        if (deviceTypeId != null && !deviceTypeId.trim().equals("")){
            timeSql+=" AND dt.device_type_id="+deviceTypeId;
        }


        //分页查询sql
        String sql = "SELECT dl.device_log_id,dl.content,DATE_FORMAT(dl.creat_time,'%Y-%m-%d %H:%i:%s')AS creat_time,d.device_name,dt.device_type_name,lt.const_desc,dc.channel_index FROM device_log_t dl,device_t d,\n" +
                "(SELECT const_value,const_desc FROM sys_const_t WHERE const_group_name='DEVICE_LOG_TYPE')lt,\n" +
                "device_type_t dt,device_channel_t dc WHERE d.device_id=dl.device_id\n" +
                "AND d.device_type_id=dt.device_type_id\n" +
                "AND lt.const_value=dl.log_type\n" +
                "AND dc.device_channel_id=dl.device_channel_id  " + timeSql + " ORDER BY device_log_id DESC LIMIT ";
        Query query = entityManager.createNativeQuery(sql + page * size + "," + size);
        List<DeviceLogVo> devicettList = query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();//转为Map
        //总数sql
        String countsql = "SELECT COUNT(dl.device_channel_id) FROM device_log_t dl,device_t d,\n" +
                "(SELECT const_value,const_desc FROM sys_const_t WHERE const_group_name='DEVICE_LOG_TYPE')lt,\n" +
                "device_type_t dt,device_channel_t dc WHERE d.device_id=dl.device_id\n" +
                "AND d.device_type_id=dt.device_type_id\n" +
                "AND lt.const_value=dl.log_type\n" +
                "AND dc.device_channel_id=dl.device_channel_id   " + timeSql;
        Query countQuery = entityManager.createNativeQuery(countsql);
        if (countQuery.getResultList().size() > 0) {
            count = countQuery.getResultList().get(0).toString();

        }

        //插入结果集
        QueryResult queryResult = new QueryResult();
        queryResult.setList(devicettList);//数据列表
        queryResult.setTotal(Integer.valueOf(count));
        QueryResponseResult queryResponseResult = new QueryResponseResult(CommonCode.SUCCESS, queryResult);
        return queryResponseResult;
    }


    /**
     *      * 设备页面列表分页查询
     *      * @param page 当前页码
     *      * @param size 页面显示个数
     *      * @param queryPageRequest 查询条件
     *      * @return 页面列表
     *      
     */
    public QueryResponseResult findDeviceList(int page, int size, QueryPageRequest queryPageRequest) {
        String count = "";

        if (queryPageRequest == null) {
            queryPageRequest = new QueryPageRequest();
        }
        //分页参数  前台有的分页是0开始 有的是1开始
        if (page <= 0) {
            page = 1;
        }
        page = page - 1;
        if (size <= 0) {
            size = 6;
        }
        //开始时间 结束时间 条件查询
        String endTime = queryPageRequest.getStopDate();
        String startTime = queryPageRequest.getBeginDate();
        String timeSql = "";
        if (startTime != null && !startTime.trim().equals("") && endTime != null && !endTime.trim().equals("")) {
            timeSql = "AND d.creat_time BETWEEN  ('" + startTime + "') AND ('" + endTime + "')";
        }

        //分页查询sql
        String sql = "SELECT d.device_id,d.device_name,DATE_FORMAT(d.creat_time,'%Y-%m-%d %H:%i:%s')AS creat_time," +
                "d.channel_num,d.longitude,d.latitude,dt.device_type_name,d.device_type_id  FROM device_t d LEFT JOIN device_type_t dt \n" +
                "ON d.device_type_id=dt.device_type_id  " + timeSql + " LIMIT ";
        Query query = entityManager.createNativeQuery(sql + page * size + "," + size);
        List<DeviceVo> devicettList = query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();//转为Map
        //总数sql
        String countsql = "SELECT COUNT(d.device_id) FROM device_t d LEFT JOIN device_type_t dt \n" +
                "ON d.device_type_id=dt.device_type_id   " + timeSql;
        Query countQuery = entityManager.createNativeQuery(countsql);
        if (countQuery.getResultList().size() > 0) {
            count = countQuery.getResultList().get(0).toString();

        }

        //插入结果集
        QueryResult queryResult = new QueryResult();
        queryResult.setList(devicettList);//数据列表
        queryResult.setTotal(Integer.valueOf(count));
        QueryResponseResult queryResponseResult = new QueryResponseResult(CommonCode.SUCCESS, queryResult);
        return queryResponseResult;
    }

    //分页报警策略配置页面列表
    public QueryResponseResult findAlarmStrategyList(int page, int size, AlarmStrategyQueryPageRequest alarmStrategyQueryPageRequest) {
        String count = "";

        if (alarmStrategyQueryPageRequest == null) {
            alarmStrategyQueryPageRequest = new AlarmStrategyQueryPageRequest();
        }
        //分页参数  前台有的分页是0开始 有的是1开始
        if (page <= 0) {
            page = 1;
        }
        page = page - 1;
        if (size <= 0) {
            size = 6;
        }
        //开始时间 结束时间 条件查询
        String endTime = alarmStrategyQueryPageRequest.getStopDate();
        String startTime = alarmStrategyQueryPageRequest.getBeginDate();
        Integer deviceId = alarmStrategyQueryPageRequest.getDeviceId();
        String timeSql = "";
        if (startTime != null && !startTime.trim().equals("") && endTime != null && !endTime.trim().equals("")) {
            timeSql = "AND c.create_time BETWEEN  ('" + startTime + "') AND ('" + endTime + "')";
        }

        //分页查询sql
        String sql = "SELECT c.*,dc.channel_index,cs.section_name FROM (\n" +
                "     SELECT ast.threshold,ast.alarm_strategy_id,d.device_name,ast.device_channel_id,ast.channel_section_id,\n" +
                "     ast.attribute_name_cn AS asattribute_name_cn,\n" +
                "     dta.device_type_id,dta.attribute_name_cn,dta.device_type_attribute_check_id,st.const_desc as stconst_desc,st.const_value AS stconst_value,DATE_FORMAT(ast.create_time,'%Y-%m-%d %H:%i:%s')AS create_time,ss.const_desc as ssconst_desc,ss.const_value AS ssconst_value,avct.attribute_value_check_type_name,avct.attribute_value_check_type_id\n" +
                "      FROM alarm_strategy_t ast,device_t d,device_type_attribute_check_t dta," +
                "attribute_value_check_type_t avct,\n" +
                "     (SELECT const_value,const_desc FROM sys_const_t WHERE const_group_name='STRATEGY_TYPE')st,\n" +
                "     (SELECT const_value,const_desc FROM sys_const_t WHERE const_group_name='STRATEGY_STATUS')ss\n" +
                "     WHERE d.device_id=ast.device_id AND dta.device_type_id=d.device_type_id \n" +
                "    AND avct.attribute_value_check_type_id=ast.attribute_value_check_type_id" +
                " AND ast.strategy_level=st.const_value AND ast.status=ss.const_value " +
                " AND dta.device_type_attribute_check_id=ast.device_type_attribute_check_id AND d.device_id="+deviceId+")c \n" +
                "     LEFT JOIN  device_channel_t dc ON dc.device_channel_id=c.device_channel_id\n" +
                "     LEFT JOIN  channel_section_t cs ON cs.channel_section_id=c.channel_section_id  " + timeSql + " LIMIT ";
        Query query = entityManager.createNativeQuery(sql + page * size + "," + size);
        List<AlarmStrategyVo> devicettList = query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();//转为Map
        //总数sql
        String countsql = "SELECT COUNT(c.alarm_strategy_id)  FROM (\n" +
                "     SELECT ast.threshold,ast.alarm_strategy_id,d.device_name,ast.device_channel_id,ast.channel_section_id,\n" +
                "     ast.attribute_name_cn AS asattribute_name_cn,\n" +
                "     dta.device_type_id,dta.attribute_name_cn,st.const_desc as stconst_desc,DATE_FORMAT(ast.create_time,'%Y-%m-%d %H:%i:%s')AS create_time,ss.const_desc as ssconst_desc\n" +
                "      FROM alarm_strategy_t ast,device_t d,device_type_attribute_check_t dta," +
                "attribute_value_check_type_t avct,\n" +
                "     (SELECT const_value,const_desc FROM sys_const_t WHERE const_group_name='STRATEGY_TYPE')st,\n" +
                "     (SELECT const_value,const_desc FROM sys_const_t WHERE const_group_name='STRATEGY_STATUS')ss\n" +
                "     WHERE d.device_id=ast.device_id AND dta.device_type_id=d.device_type_id \n" +
                "     AND avct.attribute_value_check_type_id=ast.attribute_value_check_type_id " +
                "     AND ast.strategy_level=st.const_value " +
                "AND dta.device_type_attribute_check_id=ast.device_type_attribute_check_id AND ast.status=ss.const_value AND d.device_id="+deviceId+")c \n" +
                "     LEFT JOIN  device_channel_t dc ON dc.device_channel_id=c.device_channel_id\n" +
                "     LEFT JOIN  channel_section_t cs ON cs.channel_section_id=c.channel_section_id   " + timeSql;
        Query countQuery = entityManager.createNativeQuery(countsql);
        if (countQuery.getResultList().size() > 0) {
            count = countQuery.getResultList().get(0).toString();

        }

        //插入结果集
        QueryResult queryResult = new QueryResult();
        queryResult.setList(devicettList);//数据列表
        queryResult.setTotal(Integer.valueOf(count));
        QueryResponseResult queryResponseResult = new QueryResponseResult(CommonCode.SUCCESS, queryResult);
        return queryResponseResult;
    }

    //分页查询设备类型页面列表
    public QueryResponseResult findDeviceTypeList(int page, int size) {
        //分页参数  前台有的分页是0开始 有的是1开始
        if (page <= 0) {
            page = 1;
        }
        page = page - 1;
        if (size <= 0) {
            size = 6;
        }

        //以下是正常分页写法
        Pageable pageable = PageRequest.of(page,size);
        Page<DeviceType> all = deviceTypeRepository.findAll(pageable);
        QueryResult queryResult = new QueryResult();
        queryResult.setList(all.getContent());//数据列表
        queryResult.setTotal(all.getTotalElements());//数据总记录数*/
        QueryResponseResult queryResponseResult = new QueryResponseResult(CommonCode.SUCCESS, queryResult);
        return queryResponseResult;

    }

    //分页查询用户信息页面
    public QueryResponseResult finduserList(int page, int size, QueryPageRequest queryPageRequest) {
        String count = "";

        if (queryPageRequest == null) {
            queryPageRequest = new QueryPageRequest();
        }
        //分页参数  前台有的分页是0开始 有的是1开始
        if (page <= 0) {
            page = 1;
        }
        page = page - 1;
        if (size <= 0) {
            size = 6;
        }
        //开始时间 结束时间 条件查询
        String endTime = queryPageRequest.getStopDate();
        String startTime = queryPageRequest.getBeginDate();
        String timeSql = "";
        if (startTime != null && !startTime.trim().equals("") && endTime != null && !endTime.trim().equals("")) {
            timeSql = "AND u.create_time BETWEEN  ('" + startTime + "') AND ('" + endTime + "')";
        }

       String sql="SELECT u.id,u.username,u.PASSWORD,u.NAME,u.sex,u.email,u.phone,DATE_FORMAT(u.create_time,'%Y-%m-%d %H:%i:%s')AS create_time ,DATE_FORMAT(u.update_time,'%Y-%m-%d %H:%i:%s') as update_time,c.role_name,c.role_id FROM user u LEFT JOIN (SELECT ur.role_id,r.role_name,ur.user_id FROM role r,user_role ur WHERE ur.role_id=r.id)c\n" +
               "ON u.id=c.user_id "+timeSql+" limit ";
        Query query = entityManager.createNativeQuery(sql + page * size + "," + size);
        List<UserVo> usertList = query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();//转为Map
        //总数sql
        String countsql = "SELECT COUNT(*) FROM user u where 1=1    " + timeSql;
        Query countQuery = entityManager.createNativeQuery(countsql);
        if (countQuery.getResultList().size() > 0) {
            count = countQuery.getResultList().get(0).toString();

        }

        //插入结果集
        QueryResult queryResult = new QueryResult();
        queryResult.setList(usertList);//数据列表
        queryResult.setTotal(Integer.valueOf(count));
        QueryResponseResult queryResponseResult = new QueryResponseResult(CommonCode.SUCCESS, queryResult);
        return queryResponseResult;


    }

    //分页查询角色信息页面
    public QueryResponseResult findroleList(int page, int size, QueryPageRequest queryPageRequest) {
        String count = "";

        if (queryPageRequest == null) {
            queryPageRequest = new QueryPageRequest();
        }
        //分页参数  前台有的分页是0开始 有的是1开始
        if (page <= 0) {
            page = 1;
        }
        page = page - 1;
        if (size <= 0) {
            size = 6;
        }
        //开始时间 结束时间 条件查询
        String endTime = queryPageRequest.getStopDate();
        String startTime = queryPageRequest.getBeginDate();
        String timeSql = "";
        if (startTime != null && !startTime.trim().equals("") && endTime != null && !endTime.trim().equals("")) {
            timeSql = "AND create_time BETWEEN  ('" + startTime + "') AND ('" + endTime + "')";
        }

        String sql="SELECT id,role_name,role_code,description,status,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%s')AS create_time ,DATE_FORMAT(update_time,'%Y-%m-%d %H:%i:%s') as update_time FROM role where 1=1 "+timeSql+" limit ";
        Query query = entityManager.createNativeQuery(sql + page * size + "," + size);
        List<Role> usertList = query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();//转为Map
        //总数sql
        String countsql = "SELECT COUNT(*) FROM role where 1=1    " + timeSql;
        Query countQuery = entityManager.createNativeQuery(countsql);
        if (countQuery.getResultList().size() > 0) {
            count = countQuery.getResultList().get(0).toString();

        }

        //插入结果集
        QueryResult queryResult = new QueryResult();
        queryResult.setList(usertList);//数据列表
        queryResult.setTotal(Integer.valueOf(count));
        QueryResponseResult queryResponseResult = new QueryResponseResult(CommonCode.SUCCESS, queryResult);
        return queryResponseResult;


    }
}
